package com.cjbdi.service;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.cjbdi.util.NumUtil;

/**
 * SfglcbrzsService
 */
@Service
@Transactional
public class SfglCbrzsService{
	@Autowired  
    @PersistenceContext  
    private EntityManager entityManager;// 注入EntityManager实例 
	@Autowired
	BaseService baseService;
	//法官/执行人员人均结案率概述（和去年同比） 
	/*public List getGsList(String kind, String type, String date, String fybm,String casetype){ 
    	Query query = entityManager.createNativeQuery("select date,jbfy,fymc,yj,cbr,avgyj from sfzs_result_cbr where casetype='"+casetype+"' and type='"+type+"' and date in('"+date+"','"+(Integer.parseInt(date)-1)+"','"+(Integer.parseInt(date)-2)+"') and kind='"+kind+"' and jbfy='"+fybm+"' order by date desc");  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
	public List getGsList(String kind, String type, String date, String fybm,String casetype){ 
    	Query query = entityManager.createNativeQuery("SELECT\r\n" + 
    			"	jbfy,fymc,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_cbr.DATE = '"+(Integer.parseInt(date)-2)+"' THEN cbr ELSE 0 END ) AS zsfgYearBeforeLastYear,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_cbr.DATE = '"+(Integer.parseInt(date)-1)+"' THEN cbr ELSE 0 END ) AS zsfgLastYear,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_cbr.DATE = '"+date+"' THEN cbr ELSE 0 END ) AS zsfgYear,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_cbr.DATE = '"+(Integer.parseInt(date)-2)+"' THEN yj ELSE 0 END ) AS spajYearBeforeLastYear,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_cbr.DATE = '"+(Integer.parseInt(date)-1)+"' THEN yj ELSE 0 END ) AS spajLastYear,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_cbr.DATE = '"+date+"' THEN yj ELSE 0 END ) AS spajYear,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_cbr.DATE = '"+(Integer.parseInt(date)-2)+"' THEN avgyj ELSE 0 END ) AS rjjalYearBeforeLastYear,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_cbr.DATE = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) AS rjjalLastYear,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_cbr.DATE = '"+date+"' THEN avgyj ELSE 0 END ) AS rjjalYear,\r\n" + 
    			"CASE\r\n" + 
    			"	\r\n" + 
    			"	WHEN sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-2)+"' THEN avgyj ELSE 0 END ) = 0 THEN\r\n" + 
    			"	999999 ELSE round(\r\n" + 
    			"			cast(\r\n" + 
    			"				(\r\n" + 
    			"					sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) - sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-2)+"' THEN avgyj ELSE 0 END ) \r\n" + 
    			"				) / sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-2)+"' THEN avgyj ELSE 0 END ) * 100 AS NUMERIC \r\n" + 
    			"			),\r\n" + 
    			"			2 \r\n" + 
    			"		) \r\n" + 
    			"		END AS lastyearzs,\r\n" + 
    			"CASE\r\n" + 
    			"		\r\n" + 
    			"		WHEN sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) = 0 THEN\r\n" + 
    			"			999999 ELSE round(\r\n" + 
    			"				cast(\r\n" + 
    			"					(\r\n" + 
    			"						sum( CASE WHEN sfzs_result_cbr.date = '"+date+"' THEN avgyj ELSE 0 END ) - sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) \r\n" + 
    			"					) / sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) * 100 AS NUMERIC \r\n" + 
    			"				),\r\n" + 
    			"				2 \r\n" + 
    			"			) \r\n" + 
    			"		END AS yearzs,\r\n" + 
    			"	CASE\r\n" + 
    			"			\r\n" + 
    			"			WHEN sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-2)+"' THEN avgyj ELSE 0 END ) = 0 \r\n" + 
    			"			OR sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) = 0 THEN\r\n" + 
    			"				999999 ELSE round(\r\n" + 
    			"					cast(\r\n" + 
    			"						(\r\n" + 
    			"							sum( CASE WHEN sfzs_result_cbr.date = '"+date+"' THEN avgyj ELSE 0 END ) - sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) \r\n" + 
    			"						) / sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) * 100 AS NUMERIC \r\n" + 
    			"					),\r\n" + 
    			"					2 \r\n" + 
    			"					) - round(\r\n" + 
    			"					cast(\r\n" + 
    			"						(\r\n" + 
    			"							sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) - sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-2)+"' THEN avgyj ELSE 0 END ) \r\n" + 
    			"						) / sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-2)+"' THEN avgyj ELSE 0 END ) * 100 AS NUMERIC \r\n" + 
    			"					),\r\n" + 
    			"					2 \r\n" + 
    			"				) \r\n" + 
    			"			END AS cz \r\n" + 
    			"		FROM\r\n" + 
    			"			sfzs_result_cbr\r\n" + 
    			"		WHERE\r\n" + 
    			"			type = '"+type+"' \r\n" + 
    			"			AND kind = '"+kind+"' \r\n" + 
    			"			AND casetype = '"+casetype+"' \r\n" + 
    			"			AND jbfy = '"+fybm+"' \r\n" + 
    			"		GROUP BY\r\n" + 
    			"			jbfy,fymc");  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //附表表格
    public List getFbList(String type, String date,String fybm ,String casetype, String kind){ 
    	StringBuffer sql = new StringBuffer("select jbfy,fyjc,yj,cbr,avgyj,yj2,cbr2,avgyj2,result from (SELECT jbfy,fyjc,\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+date+"' then yj else 0 end )as yj,\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+date+"' then cbr else 0 end )as cbr,\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) as avgyj,\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then yj else 0 end) as yj2,\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then cbr else 0 end )as cbr2,\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )as avgyj2,\r\n" + 
        			"CASE WHEN sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END )=0 THEN 999999 ELSE \r\n" + 
        			"round(cast( (\r\n" + 
        			"	sum( CASE WHEN sfzs_result_cbr.date = '"+date+"' THEN avgyj ELSE 0 END ) - sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) \r\n" + 
        			"	)*100/ sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) AS NUMERIC ),2) END AS result " + 
        			"FROM sfzs_result_cbr,sfzs_czrk where sfzs_result_cbr.jbfy=sfzs_czrk.fybm and sfzs_czrk.year='"+date+"' and type='"+type+"' and kind ='"+kind+"' and casetype='"+casetype+"' and ");
    	if(kind.equals("高")) {
    		sql.append("jbfy not in('X00','000','ZZ0')");
    	}else {
    		sql.append("jbfy like'"+fybm.substring(0, 1)+"%'");
    	}
    	sql.append(" GROUP BY jbfy,fyjc)t order by result desc");
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList(); 
        for(int i=0;i<rows.size();i++) {
			Map row = (Map)rows.get(i);
			row.put("id", i+1);
			row.put("lv", NumUtil.toNxiaoshu(row.get("result").toString(), 2)+"%");
		}
        return rows;
    }
    //全国排名表格
    public List getQgTableList(String type, String date,String casetype, boolean isQuanguo){  
    	StringBuffer sql = new StringBuffer();
    	if(type.equals("year")) {
    		sql = new StringBuffer("select jbfy,fy,lastyearzs,yearzs,cz,rank() over(order by @yearzs) as rank from(select jbfy,fymc as fy,lastyearzs,yearzs,cz from(SELECT jbfy,fymc,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end \r\n" + 
        			"as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as yearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 or sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2)-\r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			"as cz\r\n" + 
        			"FROM sfzs_result_cbr where (jbfy not in('X00','000') or jbfy is null)");
        	if(isQuanguo) {
        		sql.append(" and fymc='全国'");
        	}else {
        		sql.append(" and fymc!='全国'");
        	}
        	sql.append(" and type='"+type+"' and kind ='高' and casetype='"+casetype+"' GROUP BY jbfy,fymc) t)t1;");
    	}else {
    		sql = new StringBuffer("select jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(case when hbzs=999999 then 999999 else (yearzs-hbzs) end) as hbcz,rank() over(order by @yearzs) as rank from(select jbfy,fymc as fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(case when hbzs=999999 then 999999 else (yearzs-hbzs) end) as hbcz from(SELECT jbfy,fymc,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end )*100 as NUMERIC),2) end \r\n" + 
        			"as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"'and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as hbzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as yearzs\r\n" + 
        			"FROM sfzs_result_cbr where (jbfy not in('X00','000') or jbfy is null)");
        	if(isQuanguo) {
        		sql.append(" and fymc='全国'");
        	}else {
        		sql.append(" and fymc!='全国'");
        	}
        	sql.append(" and kind ='高' and casetype='"+casetype+"' GROUP BY jbfy,fymc) t)t1;");
    	}
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //中院排名表格
    public List getZjTableList(String type, String date, String fybm,String casetype){
    	String sql = "";
    	if(type.equals("year")) {
    		sql = "select jbfy,fyjc as fy,lastyearzs,yearzs,cz from(SELECT jbfy,fyjc,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100  as NUMERIC),2) end\r\n" + 
        			"as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100  as NUMERIC),2) end\r\n" + 
        			"as yearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 or sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else\r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2)-\r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			"as cz\r\n" + 
        			"FROM sfzs_result_cbr,sfzs_czrk where sfzs_result_cbr.jbfy=sfzs_czrk.fybm and type='"+type+"' and sfzs_czrk.year='"+date+"' and kind ='中' and casetype='"+casetype+"' and jbfy like'"+fybm.substring(0, 1)+"%' GROUP BY jbfy,fyjc) t order by @yearzs;";
    	}else {
    		sql = "select jbfy,fyjc as fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(case when hbzs=999999 then 999999 else (yearzs-hbzs) end) as hbcz from(SELECT jbfy,fyjc,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end )*100  as NUMERIC),2) end\r\n" + 
        			"as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"'and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as hbzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end )*100  as NUMERIC),2) end\r\n" + 
        			"as yearzs \r\n" + 
        			"FROM sfzs_result_cbr,sfzs_czrk where sfzs_result_cbr.jbfy=sfzs_czrk.fybm and sfzs_czrk.year='"+date+"' and kind ='中' and casetype='"+casetype+"' and jbfy like'"+fybm.substring(0, 1)+"%' GROUP BY jbfy,fyjc) t order by @yearzs;";
    	}  	
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //直辖市中院排名表格
    public List getZjZxTableList(String type, String date, String fybm,String casetype){
    	String sql = "";
    	if(type.equals("year")) {
    		sql = "select jbfy,fyjc as fy,lastyearzs,yearzs,cz from(SELECT jbfy,fyjc,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100  as NUMERIC),2) end\r\n" + 
        			"as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100  as NUMERIC),2) end\r\n" + 
        			"as yearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 or sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else\r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2)-\r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			"as cz\r\n" + 
        			"FROM sfzs_result_cbr,sfzs_czrk where sfzs_result_cbr.jbfy=sfzs_czrk.fybm and type='"+type+"' and sfzs_czrk.year='"+date+"' and kind ='辖' and casetype='"+casetype+"' and jbfy like'"+fybm.substring(0, 1)+"%' AND right(jbfy,1)='0' AND jbfy <>'"+fybm.substring(0, 1)+"00' GROUP BY jbfy,fyjc) t order by @yearzs;";
    	}else {
    		sql = "select jbfy,fyjc as fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(yearzs-hbzs) as hbcz from(SELECT jbfy,fyjc,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"'  then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"'  then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"'  then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"'  then avgyj else 0 end )*100  as NUMERIC),2) end\r\n" + 
        			"as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"'and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as hbzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"'  then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' and type='"+type+"'  then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"'  then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"'  then avgyj else 0 end )*100  as NUMERIC),2) end\r\n" + 
        			"as yearzs\r\n" + 
        			"FROM sfzs_result_cbr,sfzs_czrk where sfzs_result_cbr.jbfy=sfzs_czrk.fybm and sfzs_czrk.year='"+date+"' and kind ='辖' and casetype='"+casetype+"' and jbfy like'"+fybm.substring(0, 1)+"%' AND right(jbfy,1)='0' AND jbfy <>'"+fybm.substring(0, 1)+"00' GROUP BY jbfy,fyjc) t order by @yearzs;";
    	}
    	
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    /*public List getGySelfZs(String kind, String type, String date, String fybm,String casetype){ 
    	Query query = entityManager.createNativeQuery("select jbfy,fyjc as fy,lastyearzs,yearzs,cz from(SELECT jbfy,fyjc,\r\n" + 
    			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end)=0 then 999999 else\r\n" + 
    			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
    			"as lastyearzs,\r\n" + 
    			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end)=0 then 999999 else\r\n" + 
    			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
    			"as yearzs,\r\n" + 
    			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end)=0 then 999999 else\r\n" + 
    			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2) end -\r\n" + 
    			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end)=0 then 999999 else\r\n" + 
    			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
    			"as cz\r\n" + 
    			"FROM sfzs_result_cbr,sfzs_czrk where sfzs_result_cbr.jbfy=sfzs_czrk.fybm and type='"+type+"' and sfzs_czrk.year='"+date+"' and kind ='辖' and casetype='"+casetype+"' and jbfy='"+fybm+"' GROUP BY jbfy,fyjc) t order by @yearzs;");  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
    //辖区排名表格
    public List getXqTableList(String type, String date, String fybm ,String casetype){ 
    	String sql ="";
    	if(type.equals("year")) {
    		sql = "select jbfy,fy,lastyearzs,yearzs,cz from(select jbfy,replace(fymc,'人民法院','法院') as fy,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as yearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 or sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2)- round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as cz \r\n" + 
        			"FROM sfzs_result_cbr where type='"+type+"' and kind ='辖' and casetype='"+casetype+"' and jbfy like'"+fybm.substring(0, 2)+"%' and jbfy<>'"+fybm.substring(0, 2)+"'||'0' and right(fymc,5)<>'（已撤销）' GROUP BY jbfy,fymc) t order by @yearzs;";
    	}else {
    		sql = "select jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(yearzs-hbzs) as hbcz from(select jbfy,replace(fymc,'人民法院','法院') as fy,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end )*100 as NUMERIC),2) end as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"'and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as hbzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+date+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end )*100 as NUMERIC),2) end as yearzs\r\n" + 
        			"FROM sfzs_result_cbr where kind ='辖' and casetype='"+casetype+"' and jbfy like'"+fybm.substring(0, 2)+"%' and jbfy<>'"+fybm.substring(0, 2)+"'||'0' and right(fymc,5)<>'（已撤销）' GROUP BY jbfy,fymc) t order by @yearzs;";
    	}
    	
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    
    //区县法院在所属高院辖区内排名
    public List getRankInGyxq(String type, String date, String fybm ,String casetype){  
    	String sql = "SELECT\r\n" + 
    			"	rank \r\n" + 
    			"FROM\r\n" + 
    			"	(select jbfy,fy,lastyearzs,yearzs,cz,rank ( ) over ( ORDER BY @yearzs) AS rank from(select jbfy,replace(fymc,'人民法院','法院') as fy,\r\n" + 
    			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as lastyearzs,\r\n" + 
    			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as yearzs,\r\n" + 
    			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 or sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2)- round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as cz \r\n" + 
    			"FROM sfzs_result_cbr where type='"+type+"' and kind ='辖' and casetype='"+casetype+"' and jbfy like'"+fybm.substring(0, 1)+"%' and RIGHT ( jbfy, 1 ) <> '0' and right(fymc,5)<>'（已撤销）' GROUP BY jbfy,fymc) t	) t1 \r\n" + 
    					"WHERE\r\n" + 
    					"	jbfy = '"+fybm+"';";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        if(rows.size()==0) {
        	rows = Arrays.asList("0");
        }
        return rows;
    }
    
    //附表表格（区县）
    public List getFbQxList(String type, String date, String fybm ,String casetype){  
    	String sql = "select jbfy,replace(fymc,'人民法院','法院') as fyjc,yj,cbr,avgyj,yj2,cbr2,avgyj2,result from (SELECT jbfy,fymc,\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+date+"' then yj else 0 end )as yj,\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+date+"' then cbr else 0 end )as cbr,\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) as avgyj,\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then yj else 0 end) as yj2,\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then cbr else 0 end )as cbr2,\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )as avgyj2,\r\n" + 
    			"CASE WHEN sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END )=0 THEN 999999 ELSE \r\n" + 
    			"round(cast( (\r\n" + 
    			"	sum( CASE WHEN sfzs_result_cbr.date = '"+date+"' THEN avgyj ELSE 0 END ) - sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) \r\n" + 
    			"	)*100/ sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) AS NUMERIC ),2) END AS result " + 
    			"FROM sfzs_result_cbr where type='"+type+"' and kind ='辖' and casetype='"+casetype+"' and jbfy like'"+fybm.substring(0, 2)+"%' and right(fymc,5)<>'（已撤销）' GROUP BY jbfy,fymc)t order by result desc";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //附表表格（区县本身）
    public List getFbQxSelfList(String type, String date, String fybm ,String casetype){  
    	String sql = "select jbfy,replace(fymc,'人民法院','法院') as fyjc,yj,cbr,avgyj,yj2,cbr2,avgyj2,result from (SELECT jbfy,fymc,\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+date+"' then yj else 0 end )as yj,\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+date+"' then cbr else 0 end )as cbr,\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) as avgyj,\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then yj else 0 end) as yj2,\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then cbr else 0 end )as cbr2,\r\n" + 
    			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )as avgyj2,\r\n" + 
    			"CASE WHEN sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END )=0 THEN 999999 ELSE \r\n" + 
    			"round(cast( (\r\n" + 
    			"	sum( CASE WHEN sfzs_result_cbr.date = '"+date+"' THEN avgyj ELSE 0 END ) - sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) \r\n" + 
    			"	)*100/ sum( CASE WHEN sfzs_result_cbr.date = '"+(Integer.parseInt(date)-1)+"' THEN avgyj ELSE 0 END ) AS NUMERIC ),2) END AS result " + 
    			"FROM sfzs_result_cbr where type='"+type+"' and kind ='辖' and casetype='"+casetype+"' and jbfy ='"+fybm+"' GROUP BY jbfy,fymc)t order by result desc";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //同等规模城市排名表格
    public List getTdZjTableList(String type, String date, String fybm ,String casetype){  
    	String sql = "";
    	if(type.equals("year")) {
    		sql = "select jbfy,fy,lastyearzs,yearzs,cz from(select jbfy,replace(fyjc,'法院','')||'法院' AS fy,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100  as NUMERIC),2)end as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100  as NUMERIC),2)end as yearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 or sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 \r\n" + 
        			"else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100  as NUMERIC),2)-\r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100  as NUMERIC),2) end\r\n" + 
        			"as cz \r\n" + 
        			"FROM sfzs_result_cbr,sfzs_czrk where sfzs_result_cbr.jbfy=sfzs_czrk.fybm and type='"+type+"' and sfzs_czrk.year='"+date+"' and (kind = '中' or (kind='高' and fybm in('100','200','900','M00'))) and size in (select size from sfzs_czrk where sfzs_czrk.year='"+date+"' and fybm = '"+fybm+"') and casetype='"+casetype+"' GROUP BY jbfy,fyjc) t order by @yearzs;";
    	}else {
    		sql = "select jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(yearzs-hbzs) as hbcz from(select jbfy,replace(fyjc,'法院','')||'法院' AS fy,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"'  then avgyj else 0 end )=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"'  then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"'  then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"'  then avgyj else 0 end )*100  as NUMERIC),2)end as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"'and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as hbzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"'  then avgyj else 0 end )=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' and type='"+type+"'  then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"'  then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"'  then avgyj else 0 end )*100  as NUMERIC),2)end as yearzs\r\n" + 

        			"FROM sfzs_result_cbr,sfzs_czrk where sfzs_result_cbr.jbfy=sfzs_czrk.fybm and sfzs_czrk.year='"+date+"' and (kind = '中' or (kind='高' and fybm in('100','200','900','M00'))) and size in (select size from sfzs_czrk where sfzs_czrk.year='"+date+"' and fybm = '"+fybm+"') and casetype='"+casetype+"' GROUP BY jbfy,fyjc) t order by @yearzs;";
    	}
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //同等直辖市中院排名表格
   /* public List getTdZxTableList(String type, String date, String fybm ,String casetype){  
    	String sql = "select jbfy,fy,lastyearzs,yearzs,cz from(select jbfy,fyjc as fy,\r\n" + 
    			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as lastyearzs,\r\n" + 
    			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as yearzs,\r\n" + 
    			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 or sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2)- round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as cz \r\n" + 
    			"FROM sfzs_result_cbr,sfzs_czrk where sfzs_result_cbr.jbfy=sfzs_czrk.fybm and type='"+type+"' and sfzs_czrk.year='"+date+"' and kind ='中' and (substring(jbfy from 1 for 1)='1' or substring(jbfy from 1 for 1)='2' or substring(jbfy from 1 for 1)='9' or substring(jbfy from 1 for 1)='M') and casetype='"+casetype+"' GROUP BY jbfy,fyjc) t order by @yearzs;";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
    public List getTdZxTableList(String type, String date, String fybm ,String casetype){ 
    	String sql = "";
    	if(type.equals("year")) {
    		sql = "select jbfy,fy,lastyearzs,yearzs,cz from(select jbfy,fyjc as fy,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as yearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 or sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2)- round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as cz \r\n" + 
        			"FROM sfzs_result_cbr,sfzs_czrk where sfzs_result_cbr.jbfy=sfzs_czrk.fybm and type='"+type+"' and sfzs_czrk.year='"+date+"' and kind ='辖' AND (jbfy like'1%0' or jbfy like'2%0' or jbfy like'9%0' or jbfy like'M%0' )\r\n" + 
        					"	AND jbfy not in('100','200','900','M00') and casetype='"+casetype+"' GROUP BY jbfy,fyjc) t order by @yearzs;";
    	}else {
    		sql = "select jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(yearzs-hbzs) as hbcz from(select jbfy,fyjc as fy,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"'  then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"'  then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"'  then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"'  then avgyj else 0 end )*100 as NUMERIC),2) end as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"'and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as hbzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"'  then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr.date='"+date+"' and type='"+type+"'  then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"'  then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"'  then avgyj else 0 end )*100 as NUMERIC),2) end as yearzs \r\n" +  
        			"FROM sfzs_result_cbr,sfzs_czrk where sfzs_result_cbr.jbfy=sfzs_czrk.fybm and sfzs_czrk.year='"+date+"' and kind ='辖' AND (jbfy like'1%0' or jbfy like'2%0' or jbfy like'9%0' or jbfy like'M%0' )\r\n" + 
        					"	AND jbfy not in('100','200','900','M00') and casetype='"+casetype+"' GROUP BY jbfy,fyjc) t order by @yearzs;";
    	}
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //同等收案量排名表格
    public List getTdSalTableList(String type, String date, String fybm,String casetype){  
    	StringBuffer sql = new StringBuffer("select sal,jbfy,fy,result from(select sal,jbfy,replace(fyjc,'法院','')||'法院' AS fy,\r\n" + 
    			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else \r\n" + 
    			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100  as NUMERIC),2)end as result\r\n" + 
    			"FROM sfzs_result_cbr,sfzs_czrk where sfzs_result_cbr.jbfy=sfzs_czrk.fybm and type='"+type+"' and sfzs_czrk.year='"+date+"' and kind = '中' and sal in (select sal from sfzs_czrk where sfzs_czrk.year='"+date+"' and fybm = '"+fybm+"') and casetype='"+casetype+"' GROUP BY sal,jbfy,fyjc) t order by @result;");
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    
    //区县法院基本信息
    public List getQxInfo(String date, String fybm ,String casetype, String type){  
    	String sql = "select (select count(*) from sfzs_czrk where sfzs_czrk.year='"+date+"' and size in (select size from sfzs_czrk where sfzs_czrk.year='"+date+"' and fybm = '"+fybm+"')) as c,(select fyjc from sfzs_czrk where sfzs_czrk.year='"+date+"' and fybm = '"+fybm.substring(0, 1)+"'||'00') as xq,\r\n" + 
    			"(select rank from (select jbfy,fy,result,rank() over(order by @result) as rank from(select jbfy,fyjc as fy,\r\n" + 
    			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else (sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 end as result\r\n" + 
    			"FROM sfzs_result_cbr,sfzs_czrk where sfzs_result_cbr.jbfy=sfzs_czrk.fybm and sfzs_czrk.year='"+date+"' and kind ='中' and type='"+type+"' and size in (select size from sfzs_czrk where sfzs_czrk.year='"+date+"' and fybm = '"+fybm+"') and casetype='"+casetype+"' GROUP BY jbfy,fyjc) t)t1 where jbfy='"+fybm+"') as rank,fyjc,fybm,num,size from sfzs_czrk where sfzs_czrk.year='"+date+"' and fybm = '"+fybm+"'";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    
    //庭室排名表格
    public List getTsTableList(String type, String date, String fybm ,String casetype){  
    	String sql = "";
    	if(type.equals("year")) {
    		sql = "select fy,lastyearzs,yearzs,cz from(select cbspt as fy,\r\n" + 
        			"case when sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr_cbspt.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as yearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 or sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr_cbspt.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2)- round(cast((sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end as cz \r\n" + 
        			"FROM sfzs_result_cbr_cbspt where type='"+type+"' and kind ='辖' and casetype='"+casetype+"' and jbfy ='"+fybm+"' GROUP BY cbspt) t order by @yearzs;";
    	}else {
    		sql = "select fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(yearzs-hbzs) as hbcz from(select cbspt as fy,\r\n" + 
        			"case when sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end )*100 as NUMERIC),2) end as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr_cbspt.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(NumUtil.getHalfYearReportDate(date, type))-1)+"'and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as hbzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end )=0 then 999999 else round(cast((sum(case when sfzs_result_cbr_cbspt.date='"+date+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end ))/sum(case when sfzs_result_cbr_cbspt.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end )*100 as NUMERIC),2) end as yearzs \r\n" + 
        			"FROM sfzs_result_cbr_cbspt where kind ='辖' and casetype='"+casetype+"' and jbfy ='"+fybm+"' GROUP BY cbspt) t order by @yearzs;";
    	}
    	
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    public void getSpZxgzlzsGy(Map dataMap,String kind,String type, String date, String fybm,String fymc,String casetype) {//法官工作量变化指数
    	getSectionOne(dataMap,kind,type,date,fybm,casetype);
    	List qgTableList = getQgTableList(type, date, casetype, false);
		List qgSelfList = getQgTableList(type, date, casetype, true);
		List qgTableList3 = new ArrayList();
		for(int i=qgTableList.size()-1;i>=0;i--) {//为全国报告增加
			Map row = (Map)qgTableList.get(i);
			String fy = row.get("fy").toString();
			if(fy.contains("北京")||fy.contains("天津")||fy.contains("上海")||fy.contains("重庆")) {//取第一个不是直辖市的高院
				continue;
			}else {
				dataMap.put("sfgl_"+casetype+"gzl_rank1_qg",NumUtil.toSheng(fy)+"法院");
				if(fymc.equals("全国")) {
					List zjList = getZjTableList(type, date, row.get("jbfy").toString(),casetype);
					Map rowzj = (Map)zjList.get(0);
					String rankZJ1 = rowzj.get("fy").toString();
					if(casetype.equals("sp")) {
						dataMap.put(25,NumUtil.toSheng(fy));
					}else {
						dataMap.put(27,NumUtil.toSheng(fy));
					}
					dataMap.put("sfgl_"+casetype+"gzl_rank1_zj", rankZJ1);//中级排名第一
				}
				break;
			}
		}
		baseService.getGyDataIntoMap(dataMap, qgTableList, qgSelfList, qgTableList3, fymc, "sfgl_"+casetype+"gzl");
		List zjTableList = getZjTableList(type, date, fybm,casetype);
		List gylist = getSelfZs(type, date,fybm, casetype);
		baseService.getZyDetailDataIntoMap(dataMap, zjTableList, gylist, fymc, "sfgl_"+casetype+"gzl");
		//处理表格
		if(casetype.equals("sp")) {
			if(fymc.equals("全国")) {
				dataMap.put("array12",qgTableList);
				List fbList = getFbList(type,date,fybm,casetype,"高");
				dataMap.put("array47",fbList);
			}else {
				List fbList = getFbList(type,date,fybm,casetype,"中");
				dataMap.put("array23",qgTableList3);
				dataMap.put("array24",zjTableList);
				dataMap.put("array48",fbList);
			}
				
		}else {
			if(fymc.equals("全国")) {
				dataMap.put("array13",qgTableList);
				List fbList = getFbList(type,date,fybm,casetype,"高");
				dataMap.put("array48",fbList);
			}else {
				List fbList = getFbList(type,date,fybm,casetype,"中");
				dataMap.put("array25",qgTableList3);
				dataMap.put("array26",zjTableList);
				dataMap.put("array49",fbList);
			}
			
		}		
	} 
    public void getSpZxgzlzsZy(Map dataMap,String kind, String type, String date, String fybm ,String fymc,String casetype) {//裁判文书公开指数    	
		List zjTableList = new ArrayList();
		List tdTableList = new ArrayList();
		List xqTableList = new ArrayList(); 
		List tsTableList = new ArrayList();
		xqTableList = getXqTableList(type, date, fybm, casetype);
		List zylist = getSelfZs(type, date,fybm, casetype);
		baseService.getQxDetailDataIntoMap(dataMap, xqTableList, zylist, fymc, "sfgl_"+casetype+"gzl");
		
		tsTableList = getTsTableList(type, date, fybm , casetype);
    	baseService.getTsDataIntoMap(dataMap, tsTableList, fybm, "sfgl_"+casetype+"gzl");
    	
		if(fybm.startsWith("1")||fybm.startsWith("2")||fybm.startsWith("M")||fybm.startsWith("9")) {
			getSectionOne(dataMap,"辖",type,date,fybm,casetype);
			tdTableList = getTdZxTableList(type, date, fybm, casetype);
			baseService.getTdZxDataIntoMap(dataMap, tdTableList, fybm, "sfgl_"+casetype+"gzl");
			zjTableList = getZjZxTableList(type, date, fybm, casetype);
			baseService.getZyDataIntoMap(dataMap, zjTableList, fybm, "sfgl_"+casetype+"gzl");
		}else {
			getSectionOne(dataMap,kind,type,date,fybm,casetype);
			//在同等规模城市中排名
			List infoList = getQxInfo(date, fybm, casetype, type);
			tdTableList = getTdZjTableList(type, date, fybm, casetype);
			baseService.getTdCityDataIntoMap(dataMap, infoList, tdTableList, "sfgl_"+casetype+"gzl",fybm);
			zjTableList = getZjTableList(type, date, fybm, casetype);
			baseService.getZyDataIntoMap(dataMap, zjTableList, fybm, "sfgl_"+casetype+"gzl");
			// 同等收案量排行
			List tdSalTableList = getTdSalTableList(type, date, fybm, casetype);
			baseService.getTdSalDataIntoMap(dataMap, tdSalTableList, fymc,"sfgl_"+casetype+"gzl");
			
		}
		
		List fbList = getFbQxList(type,date,fybm, casetype);
		baseService.handleFb(fbList, fymc);
		//处理表格
		if(casetype.equals("sp")) {
			
			if(fybm.startsWith("1")||fybm.startsWith("2")||fybm.startsWith("M")||fybm.startsWith("9")) {
				dataMap.put("array38",zjTableList);
				dataMap.put("array39",tdTableList);
				dataMap.put("array40",xqTableList);
				dataMap.put("array41",tsTableList);
				dataMap.put("array72",fbList);  
			}else {
				dataMap.put("array42",zjTableList);
				dataMap.put("array43",tdTableList);
				dataMap.put("array44",xqTableList);
				dataMap.put("array45",tsTableList);
				dataMap.put("array77",fbList); 
			} 
		}else {
			if(fybm.startsWith("1")||fybm.startsWith("2")||fybm.startsWith("M")||fybm.startsWith("9")) {
				dataMap.put("array42",zjTableList);
				dataMap.put("array43",tdTableList);
				dataMap.put("array44",xqTableList);
				dataMap.put("array45",tsTableList);
				dataMap.put("array73",fbList); 
			}else {
				dataMap.put("array46",zjTableList);
				dataMap.put("array47",tdTableList);
				dataMap.put("array48",xqTableList);
				dataMap.put("array49",tsTableList);
				dataMap.put("array78",fbList); 
			}
		}
		
	}   
    
    public void getQxZs(Map dataMap,String kind, String type, String date, String fybm,String casetype) {//区县法院指数
    	getSectionOne(dataMap, kind, type, date, fybm, casetype);
    	List xqTableList = getXqTableList(type, date, fybm, casetype);
    	List gylist = getGyOrZyXqZs(type, date,fybm, casetype,"高");
    	List zylist = getGyOrZyXqZs(type, date,fybm, casetype,"中");
    	baseService.getQxDataIntoMap(dataMap, xqTableList, fybm, "sfgl_"+casetype+"gzl",gylist,zylist);
    	String ssxq_gy_rank = getRankInGyxq(type,date,fybm,casetype).get(0).toString();
    	dataMap.put("sfgl_"+casetype+"gzl_rank_gy",  "第" + NumUtil.toChineseNum(ssxq_gy_rank) + "位");
    	List fbList = getFbQxSelfList(type,date,fybm, casetype);
    	List tsTableList = getTsTableList(type, date, fybm , casetype);
    	baseService.getTsDataIntoMap(dataMap, tsTableList, fybm, "sfgl_"+casetype+"gzl");
		for(int i=0;i<fbList.size();i++) {
			Map row = (Map)fbList.get(i);
			row.put("id", i+1);
			row.put("lv", NumUtil.toNxiaoshu(row.get("result").toString(), 2)+"%");
		}
    	if(casetype.equals("sp")) {
    		dataMap.put("array20",xqTableList);
    		dataMap.put("array21",tsTableList);
    		dataMap.put("array41",fbList); 
    	}else {
    		dataMap.put("array22",xqTableList);
    		dataMap.put("array23",tsTableList);
    		dataMap.put("array42",fbList); 
    	}	
    }
      
    public  void getSectionOne(Map dataMap,String kind,String type, String date, String fybm,String casetype) {
    	List gslist = getGsList(kind,type,date,fybm,casetype);//获取法官/执行人员指数概况
    	int zsfg = 0;//主审法官数
    	int spaj = 0;//结案量
    	double rjjal = 0;//人均结案量
    	int zsfgLastYear = 0;
    	int spajLastYear = 0;    	
    	double rjjalLastYear = 0;
    	int zsfgYearBeforeLastYear = 0;
    	int spajYearBeforeLastYear = 0;    	
    	double rjjalYearBeforeLastYear = 0;
    	double zs = 0;
    	double zsLastYear = 0;
    	if(gslist.size()>0) {
    		Map map = (Map)gslist.get(0);
        	zsfg = Integer.parseInt(map.get("zsfgyear").toString());//主审法官数
        	spaj = Integer.parseInt(map.get("spajyear").toString());//结案量
        	rjjal = NumUtil.toNxiaoshu(map.get("rjjalyear").toString(), 2);//人均结案量
        	zsfgLastYear = Integer.parseInt(map.get("zsfglastyear").toString());
        	spajLastYear = Integer.parseInt(map.get("spajlastyear").toString());    	
        	rjjalLastYear = NumUtil.toNxiaoshu(map.get("rjjallastyear").toString(), 2);
        	zsfgYearBeforeLastYear = Integer.parseInt(map.get("zsfgyearbeforelastyear").toString());
        	spajYearBeforeLastYear = Integer.parseInt(map.get("spajyearbeforelastyear").toString());    	
        	rjjalYearBeforeLastYear = NumUtil.toNxiaoshu(map.get("rjjalyearbeforelastyear").toString(), 2);
        	zs = NumUtil.toNxiaoshu(map.get("yearzs").toString(), 2);
        	zsLastYear = NumUtil.toNxiaoshu(map.get("lastyearzs").toString(), 2); 	
    	}
    	if(zs>0) {
    		dataMap.put("sfgl_"+casetype+"gzlzs_qs", "增长"+Math.abs(zs)+"%");
    	}else if(zs<0) {
    		dataMap.put("sfgl_"+casetype+"gzlzs_qs", "下降"+Math.abs(zs)+"%");
    	}else {
    		dataMap.put("sfgl_"+casetype+"gzlzs_qs", "不变");
    	}
    	dataMap.put("sfgl_"+casetype+"gzlzs", zs);
		dataMap.put("sfgl_"+casetype+"gzlzs_lastyear", zsLastYear);
		
		dataMap.put("sfgl_"+casetype+"gzl_zsfg", NumUtil.toWan(zsfg));
		dataMap.put("sfgl_"+casetype+"gzl_zsfg_lastyear", NumUtil.toWan(zsfgLastYear));
		dataMap.put("sfgl_"+casetype+"gzl_zsfg_yearbeforelastyear", NumUtil.toWan(zsfgYearBeforeLastYear));
		
		dataMap.put("sfgl_"+casetype+"gzl_spaj", NumUtil.toWan(spaj));
		dataMap.put("sfgl_"+casetype+"gzl_spaj_lastyear", NumUtil.toWan(spajLastYear));
		dataMap.put("sfgl_"+casetype+"gzl_spaj_yearbeforelastyear", NumUtil.toWan(spajYearBeforeLastYear));
		
		dataMap.put("sfgl_"+casetype+"gzl_rjja", rjjal);
		dataMap.put("sfgl_"+casetype+"gzl_rjja_lastyear", rjjalLastYear);
		dataMap.put("sfgl_"+casetype+"gzl_rjja_yearbeforelastyear", rjjalYearBeforeLastYear);
		if(rjjal>rjjalLastYear) {
			dataMap.put("sfgl_"+casetype+"gzl_rjja_cz", "增加"+(NumUtil.toNxiaoshu(rjjal-rjjalLastYear,2)));//同比
			dataMap.put("sfgl_"+casetype+"gzl_rjja_tb", "上升"+ Math.abs(zs) +"%");//同比
		}else if(rjjal<rjjalLastYear) {
			dataMap.put("sfgl_"+casetype+"gzl_rjja_cz", "减少"+(NumUtil.toNxiaoshu(rjjalLastYear-rjjal,2)));//同比
			dataMap.put("sfgl_"+casetype+"gzl_rjja_tb", "下降"+ Math.abs(zs) +"%");//同比
		}else {
			dataMap.put("sfgl_"+casetype+"gzl_rjja_cz", "增加0");
			dataMap.put("sfgl_"+casetype+"gzl_rjja_tb", "不变");//同比
		}
		
		if(zs>zsLastYear) {
			dataMap.put("sfgl_"+casetype+"gzl_tb", "上升"+(NumUtil.toNxiaoshu(zs-zsLastYear,2)));//同比
		}else if(zs<zsLastYear) {
			dataMap.put("sfgl_"+casetype+"gzl_tb", "下降"+(NumUtil.toNxiaoshu(zsLastYear-zs,2)));//同比
		}else {
			dataMap.put("sfgl_"+casetype+"gzl_tb", "不变");//同比
		}
		if(kind.equals("中")) {
			Map zyxq = new HashMap();
			zyxq.put("fy", dataMap.get("fy")+"法院");
			zyxq.put("lastyearzs",zs);
			zyxq.put("yearzs",zsLastYear);
			zyxq.put("cz",NumUtil.toNxiaoshu(zs-zsLastYear,2));
			dataMap.put("zyxq", zyxq);
		}
    }
    public List getSelfZs(String type, String date,String fybm, String casetype){  
    	StringBuffer sql = new StringBuffer();
    	if(type.equals("year")) {
    		sql = new StringBuffer("SELECT jbfy,fymc as fy,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end \r\n" + 
        			"as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as yearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 or sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2)-\r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			"as cz\r\n" + 
        			"FROM sfzs_result_cbr where jbfy='"+fybm+"'");
        	sql.append(" and type='"+type+"' and kind ='辖' and casetype='"+casetype+"' GROUP BY jbfy,fymc;");
    	}else {
    		sql = new StringBuffer("SELECT jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(yearzs-hbzs) as hbcz from(SELECT jbfy,fymc as fy,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end )*100 as NUMERIC),2) end \r\n" + 
        			"as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as hbzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as yearzs\r\n" + 
        			"FROM sfzs_result_cbr where jbfy='"+fybm+"'");
        	sql.append(" and kind ='辖' and casetype='"+casetype+"' GROUP BY jbfy,fymc)t;");
    	}
    	
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    public List getGyOrZyXqZs(String type, String date,String fybm, String casetype,String kind){  
    	StringBuffer sql = new StringBuffer();
    	if(type.equals("year")) {
    		sql = new StringBuffer("SELECT jbfy,fymc as fy,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end \r\n" + 
        			"as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as yearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )=0 or sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end )*100 as NUMERIC),2)-\r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			"as cz\r\n" + 
        			"FROM sfzs_result_cbr where ");
    		if(kind.equals("高")) {
    			sql.append("jbfy='"+fybm.substring(0, 1)+"'||'00' ");
    		}else {
    			sql.append("jbfy='"+fybm.substring(0, 2)+"'||'0' ");
    		}
        	sql.append(" and type='"+type+"' and kind ='"+kind+"' and casetype='"+casetype+"' GROUP BY jbfy,fymc;");
    	}else {
    		sql = new StringBuffer("SELECT jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(yearzs-hbzs) as hbcz from(SELECT jbfy,fymc as fy,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-2)+"' and type='"+type+"' then avgyj else 0 end )*100 as NUMERIC),2) end \r\n" + 
        			"as lastyearzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as hbzs,\r\n" + 
        			"case when sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end)=0 then 999999 else \r\n" + 
        			"round(cast((sum(case when sfzs_result_cbr.date='"+date+"' and type='"+type+"' then avgyj else 0 end) - sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end ))/\r\n" + 
        			"sum(case when sfzs_result_cbr.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then avgyj else 0 end )*100 as NUMERIC),2) end\r\n" + 
        			" as yearzs\r\n" + 
        			"FROM sfzs_result_cbr where ");
    		if(kind.equals("高")) {
    			sql.append("jbfy='"+fybm.substring(0, 1)+"'||'00' ");
    		}else {
    			sql.append("jbfy='"+fybm.substring(0, 2)+"'||'0' ");
    		}
        	sql.append(" and kind ='"+kind+"' and casetype='"+casetype+"' GROUP BY jbfy,fymc)t;");
    	}
    	
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
}
